package com.cxx.purchasecharge.dal.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.cxx.purchasecharge.core.model.PaymentSearchForm;
import com.cxx.purchasecharge.core.model.persistence.Payment;
import com.cxx.purchasecharge.dal.PaymentDaoPlus;
import com.cxx.purchasecharge.dal.common.GenericDaoUtils;

public class PaymentDaoImpl implements PaymentDaoPlus
{
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page <Payment> findPagePaymentBy (PaymentSearchForm searchForm, Pageable pageable)
    {
        String selectCountSql = "SELECT count(p) ";
        String fromSql = "FROM Payment p where ";
        String conditionSql = "";
        if (null != searchForm)
        {
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                && 0 != searchForm.getCustomerId () && 0 != searchForm.getPaymentId ())
            {
                conditionSql += "p.paidDate between ?1 and ?2 and p.customer.id = ?3 and p.paymentAccount.id = ?4 ";
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                     && 0 != searchForm.getCustomerId ())
            {
                conditionSql += "p.paidDate between ?1 and ?2 and p.customer.id = ?3 ";
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                     && 0 != searchForm.getPaymentId ())
            {
                conditionSql += "p.paidDate between ?1 and ?2 and p.paymentAccount.id = ?3 ";
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                conditionSql += "p.paidDate between ?1 and ?2 and p.customer.typeCode = ?3 ";
            }
            else if (0 != searchForm.getCustomerId () && 0 != searchForm.getPaymentId ())
            {
                conditionSql += "p.customer.id = ?1 and p.paymentAccount.id = ?2 ";
            }
            else if (0 != searchForm.getCustomerId ())
            {
                conditionSql += "p.customer.id = ?1 ";
            }
            else if (0 != searchForm.getPaymentId ())
            {
                conditionSql += "p.paymentAccount.id = ?1 ";
            }
        }
        Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
        if (null != searchForm)
        {
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                && 0 != searchForm.getCustomerId () && 0 != searchForm.getPaymentId ())
            {
                countQuery.setParameter (1, searchForm.getStartDate ());
                countQuery.setParameter (2, searchForm.getEndDate ());
                countQuery.setParameter (3, searchForm.getCustomerId ());
                countQuery.setParameter (4, searchForm.getPaymentId ());
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                     && 0 != searchForm.getCustomerId ())
            {
                countQuery.setParameter (1, searchForm.getStartDate ());
                countQuery.setParameter (2, searchForm.getEndDate ());
                countQuery.setParameter (3, searchForm.getCustomerId ());
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                     && 0 != searchForm.getPaymentId ())
            {
                countQuery.setParameter (1, searchForm.getStartDate ());
                countQuery.setParameter (2, searchForm.getEndDate ());
                countQuery.setParameter (3, searchForm.getPaymentId ());
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                countQuery.setParameter (1, searchForm.getStartDate ());
                countQuery.setParameter (2, searchForm.getEndDate ());
                countQuery.setParameter (3, searchForm.getTypeCode ());
            }
            else if (0 != searchForm.getCustomerId () && 0 != searchForm.getPaymentId ())
            {
                countQuery.setParameter (1, searchForm.getCustomerId ());
                countQuery.setParameter (2, searchForm.getPaymentId ());
            }
            else if (0 != searchForm.getCustomerId ())
            {
                countQuery.setParameter (1, searchForm.getCustomerId ());
            }
            else if (0 != searchForm.getPaymentId ())
            {
                countQuery.setParameter (1, searchForm.getPaymentId ());
            }
        }
        int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

        String querySql = "SELECT p ";
        querySql += fromSql;
        querySql += conditionSql;
        String orderBySql = " ORDER BY ";
        querySql += orderBySql;
        String sortSqlStr = GenericDaoUtils.parseSort (Payment.class, pageable, "p");
        if (!"".endsWith (sortSqlStr))
        {
            querySql += sortSqlStr;
        }
        else
        {
            querySql += "p.paidDate desc";
        }
        Query query = entityManager.createQuery (querySql);
        if (null != searchForm)
        {
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                && 0 != searchForm.getCustomerId () && 0 != searchForm.getPaymentId ())
            {
                query.setParameter (1, searchForm.getStartDate ());
                query.setParameter (2, searchForm.getEndDate ());
                query.setParameter (3, searchForm.getCustomerId ());
                query.setParameter (4, searchForm.getPaymentId ());
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                     && 0 != searchForm.getCustomerId ())
            {
                query.setParameter (1, searchForm.getStartDate ());
                query.setParameter (2, searchForm.getEndDate ());
                query.setParameter (3, searchForm.getCustomerId ());
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                     && 0 != searchForm.getPaymentId ())
            {
                query.setParameter (1, searchForm.getStartDate ());
                query.setParameter (2, searchForm.getEndDate ());
                query.setParameter (3, searchForm.getPaymentId ());
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                query.setParameter (1, searchForm.getStartDate ());
                query.setParameter (2, searchForm.getEndDate ());
                query.setParameter (3, searchForm.getTypeCode ());
            }
            else if (0 != searchForm.getCustomerId () && 0 != searchForm.getPaymentId ())
            {
                query.setParameter (1, searchForm.getCustomerId ());
                query.setParameter (2, searchForm.getPaymentId ());
            }
            else if (0 != searchForm.getPaymentId ())
            {
                query.setParameter (1, searchForm.getPaymentId ());
            }
            else if (0 != searchForm.getCustomerId ())
            {
                query.setParameter (1, searchForm.getCustomerId ());
            }
        }
        query.setFirstResult (pageable.getOffset ());
        query.setMaxResults (pageable.getPageSize ());

        @SuppressWarnings ("unchecked")
        List <Payment> payments = query.getResultList ();
        // lazy load customer of payment
        for(Payment payment : payments) 
        {
            payment.getCustomer ();
        }
        Page <Payment> paymentPage = new PageImpl <Payment> (payments, pageable, total);
        return paymentPage;
    }

    @Override
    public float countPaidBy (PaymentSearchForm searchForm)
    {
        String selectCountSql = "SELECT sum(p.paid) ";
        String fromSql = "FROM Payment p where p.paid > 0 ";
        String conditionSql = "";
        if (null != searchForm)
        {
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                && 0 != searchForm.getCustomerId ())
            {
                conditionSql += "and p.paidDate between ?1 and ?2 and p.customer.id = ?3 ";
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                conditionSql += "and p.paidDate between ?1 and ?2 and p.customer.typeCode = ?3 ";
            }
            else if (0 != searchForm.getCustomerId ())
            {
                conditionSql += "and p.customer.id = ?1 ";
            }
        }

        String countSql = selectCountSql + fromSql + conditionSql;
        Query countQuery = entityManager.createQuery (countSql);
        if (null != searchForm)
        {
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                && 0 != searchForm.getCustomerId ())
            {
                countQuery.setParameter (1, searchForm.getStartDate ());
                countQuery.setParameter (2, searchForm.getEndDate ());
                countQuery.setParameter (3, searchForm.getCustomerId ());
            }
            else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                countQuery.setParameter (1, searchForm.getStartDate ());
                countQuery.setParameter (2, searchForm.getEndDate ());
                countQuery.setParameter (3, searchForm.getTypeCode ());
            }
            else if (0 != searchForm.getCustomerId ())
            {
                countQuery.setParameter (1, searchForm.getCustomerId ());
            }
        }

        float sumPaid = Float.parseFloat (countQuery.getResultList ().get (0).toString ());
        return sumPaid;
    }

}
